
libname inn "D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis";

data final_gam_modeldata;
set inn.final_gam_modeldata_ecooker;
run;
proc contents data = final_gam_modeldata;
run;

/***********BREVILLE ******************/

data buyboxwinner_ID_brev;
set final_gam_modeldata;
if priceAmzn_Breville = buyboxprice_breville and 
priceclust4_Breville ne buyboxprice_breville then buybox_winamzn_brev = 1; else buybox_winamzn_brev = 0;

if priceAmzn_Breville ne buyboxprice_breville and 
priceclust4_Breville = buyboxprice_breville then buybox_winfocal3p_brev = 1; else buybox_winfocal3p_brev = 0;

if priceAmzn_Breville ne buyboxprice_breville and 
priceclust4_Breville ne buyboxprice_breville and buyboxprice_breville not in (., 0) then buybox_winother3p_brev = 1; else buybox_winother3p_brev = 0;

run;
proc univariate data = buyboxwinner_ID_brev;
var buyboxprice_breville;
run;
/**when buy box is visible*/
data buyboxvisible_breville;
set buyboxwinner_ID_brev;
if buyboxprice_breville > 0;
run;

proc freq data = buyboxwinner_ID_brev;
tables buybox_winamzn_brev*buybox_winother3p_brev*buybox_winfocal3p_brev /list;
run;

/**days with clear winner ***/
data buyboxwinner_ID_brev1;
set buyboxwinner_ID_brev;
where buybox_winamzn_brev = 1 or buybox_winfocal3p_brev = 1 or buybox_winother3p_brev = 1;
run;
proc sgplot data=buyboxwinner_ID_brev1;
  series x=time1 y=priceAmzn_Breville / lineattrs=(color=blue pattern=shortdash thickness=2) legendlabel="Amazon";
  series x=time1 y=priceclust4_Breville / lineattrs=(color=red pattern=shortdash thickness=1) legendlabel="Primary 3p";
  series x=time1 y=buyboxprice_breville / lineattrs=(color=black thickness=1) legendlabel="Buy Box Price";
  xaxis label="Day";
  yaxis label="Amazon, Primary 3p and Buy Box Prices";
  title "Amazon, Primary 3p and Buy Box Prices - Breville, E.Cookers";
run;

proc freq data = buyboxwinner_ID_brev1;
tables buybox_winamzn_brev*buybox_winother3p_brev*buybox_winfocal3p_brev /list;
run;


proc sql;
create table summary_tab as 
select
    count(case when priceAmzn_Breville ne . then time1 end) as numdays_Amzlisted,
    count(case when priceclust4_Breville ne . then time1 end) as numdays_primary3plisted,
    count(case when priceAmzn_Breville ne . and priceclust4_Breville ne . then time1 end) as numdays_bothlisted,
    count(case when priceAmzn_Breville > priceclust4_Breville then time1 end) as numdays_primarylessAmzn,
    count(case when buybox_winamzn_brev = 1 and priceAmzn_Breville > priceclust4_Breville then time1 end) as numdays_AmznBBoxwin_athigh,
    mean(case when priceAmzn_Breville = . then priceclust4_Breville end) as meanprimaryprice_amzdelisted,
    mean(case when priceAmzn_Breville > 0 then priceclust4_Breville end) as meanprimaryprice_amzlisted,
	mean(case when priceclust4_Breville = . then priceAmzn_Breville end) as meanAmzprice_primarydelisted,
    mean(case when priceclust4_Breville > 0 then priceAmzn_Breville end) as meanAmzprice_primarylisted
from buyboxwinner_ID_brev1;
quit;

proc print data = summary_tab;
run;
PROC CONTENTS DATA = final_gam_modeldata;
RUN;

/**Modeling Buy Box win for Amazon and 3p BREVILLE****/

data buyboxwinner_ID_brev2;
set buyboxwinner_ID_brev1;
if buybox_winamzn_brev = 1 and buybox_winfocal3p_brev =  0 then buyboxwinner = 1; /**amazon wins**/
else if buybox_winamzn_brev = 0 and buybox_winfocal3p_brev = 1 then buyboxwinner = 2; /**primary 3p wins **/
else buyboxwinner = 0; /**other 3p wins**/
rename Y_clust4_breville_lag1 = Y_clust4_brev_lag1;
run;
proc freq data = buyboxwinner_ID_brev2;
tables buybox_winamzn_brev*buybox_winfocal3p_brev /list;
run;
proc logistic data=buyboxwinner_ID_brev2;
    class buyboxwinner (ref = "0" ) / param = ref;
    model buyboxwinner = priceAmzn_Breville priceclust4_Breville  
/*          Y_amzn_breville_lag1 Y_clust4_brev_lag1 */
          / link=glogit;
run;


/******ELITE PLATINUM ******************/

/**taking only the new items**/
data buyboxwinner_ID_EP;
set final_gam_modeldata;
if priceAmzn_EP = buyboxprice_EP and 
priceclust2_EP ne buyboxprice_EP then buybox_winamzn_EP = 1; else buybox_winamzn_EP = 0;

if priceAmzn_EP ne buyboxprice_EP and 
priceclust2_EP = buyboxprice_EP then buybox_winsecond3p_EP = 1; else buybox_winsecond3p_EP = 0;

if priceAmzn_EP ne buyboxprice_EP and 
priceclust2_EP ne buyboxprice_EP and buyboxprice_EP not in (., 0) then buybox_winother3p_EP = 1; else buybox_winother3p_EP = 0;

run;
proc univariate data = buyboxwinner_ID_ep;
var priceAmzn_EP;
run;

proc freq data = buyboxwinner_ID_ep;
tables buybox_winamzn_EP*buybox_winother3p_EP*buybox_winsecond3p_EP /list;
run;

/**days with clear winner ***/
data buyboxwinner_ID_ep1;
set buyboxwinner_ID_ep;
where buybox_winamzn_EP = 1 or buybox_winsecond3p_EP = 1 or buybox_winother3p_EP = 1;
run;

proc freq data = buyboxwinner_ID_ep1;
tables buybox_winamzn_EP*buybox_winother3p_EP*buybox_winsecond3p_EP /list;
run;


proc sql;
create table summary_tab_ep as 
select
    count(case when priceAmzn_EP ne . then time1 end) as numdays_Amzlisted,
    count(case when priceclust2_EP ne . then time1 end) as numdays_secondary3plisted,
    count(case when priceAmzn_EP ne . and priceclust2_EP ne . then time1 end) as numdays_bothlisted,
    count(case when priceAmzn_EP > priceclust2_EP then time1 end) as numdays_secondarylessAmzn,
    count(case when buybox_winamzn_EP = 1 and priceAmzn_EP > priceclust2_EP then time1 end) as numdays_AmznBBoxwin_athigh,
    mean(case when priceAmzn_EP = . then priceclust2_EP end) as meansecondprice_amzdelisted,
    mean(case when priceAmzn_EP > 0 then priceclust2_EP end) as meansecondprice_amzlisted,
	mean(case when priceclust2_EP = . then priceAmzn_EP end) as meanAmzprice_secondarydelisted,
    mean(case when priceclust2_EP > 0 then priceAmzn_EP end) as meanAmzprice_secondarylisted
from buyboxwinner_ID_EP1;
quit;

proc print data = summary_tab_ep;
run;



/**Modeling Buy Box win for Amazon and 3p ELITE PLATINUM****/

data buyboxwinner_ID_EP12;
set buyboxwinner_ID_EP1;
if buybox_winamzn_EP = 1 and buybox_winsecond3p_EP =  0 then buyboxwinner = 1; /**amazon wins**/
else if buybox_winamzn_EP = 0 and buybox_winsecond3p_EP = 1 then buyboxwinner = 2; /**secondary 3p wins **/
else buyboxwinner = 0; /**other 3p wins**/
run;
proc freq data = buyboxwinner_ID_EP12;
tables buyboxwinner;
run;

proc logistic data=buyboxwinner_ID_EP12;
    class buybox_winamzn_EP;
    model buybox_winamzn_EP = priceAmzn_EP priceclust2_EP Y_amzn_ep_lag1 Y_clust2_ep_lag1 / link=clogit;
run;

/**cannot be modeled since there is no variation in buyboxwinner **/


/***********CROCK POT ******************/

data buyboxwinner_ID_crockp;
set final_gam_modeldata;
if priceAmzn_crockp = buyboxprice_crockp and 
priceclust3_CrockP ne buyboxprice_crockp and
priceclust5_CrockP ne buyboxprice_crockp then buybox_winamzn_crockp = 1; else buybox_winamzn_crockp = 0;


if priceclust3_crockp = buyboxprice_crockp or priceclust5_crockp = buyboxprice_crockp 
then buybox_winfocal3p_crockp = 1; 

else if priceAmzn_crockp ne buyboxprice_crockp and 
priceclust3_crockp ne buyboxprice_crockp and priceclust5_crockp ne buyboxprice_crockp and
buyboxprice_crockp not in (., 0) then buybox_winother3p_crockp = 1; 


run;


/**days with clear winner ***/
data buyboxwinner_ID_crockp1;
set buyboxwinner_ID_crockp;
where buybox_winamzn_crockp = 1 or buybox_winfocal3p_crockp = 1 or buybox_winother3p_crockp = 1;
run;
proc print data = buyboxwinner_ID_crockp1 (obs=20);
var  priceAmzn_crockp priceclust3_crockp priceclust5_crockp buyboxprice_crockp ;
where buybox_winother3p_crockp = 1;
run;
proc freq data = buyboxwinner_ID_crockp1;
tables buybox_winamzn_crockp*buybox_winfocal3p_crockp*buybox_winother3p_crockp /list;
run;

proc sgplot data=buyboxwinner_ID_crockp1;
  series x=time1 y=priceAmzn_crockp / lineattrs=(color=blue pattern=shortdash thickness=2) legendlabel="Amazon";
  series x=time1 y=priceclust3_crockp / lineattrs=(color=red pattern=shortdash thickness=1) legendlabel="Primary 3p";
  series x=time1 y=buyboxprice_crockp / lineattrs=(color=black thickness=1) legendlabel="Buy Box Price";
  xaxis label="Day";
  yaxis label="Amazon, Primary 3p and Buy Box Prices";
  title "Amazon, Primary 3p and Buy Box Prices - Crock Pot, E.Cookers";
run;
proc sql;
create table summary_tab_crockp as 
select
    count(case when priceAmzn_crockp ne . then time1 end) as numdays_Amzlisted,
    count(case when priceclust3_crockp ne . then time1 end) as numdays_primary3p1listed,
	count(case when priceclust5_crockp ne . then time1 end) as numdays_primary3p2listed,
    count(case when priceAmzn_crockp ne . and priceclust3_crockp ne . and priceclust5_crockp ne . then time1 end) as numdays_bothlisted,
    count(case when priceAmzn_crockp > priceclust3_crockp and priceAmzn_crockp > priceclust5_crockp then time1 end) as numdays_primarylessAmzn,
/*    count(case when buybox_winamzn_crockp = 1 and priceAmzn_crockp > priceclust2_crockp then time1 end) as numdays_AmznBBoxwin_athigh,*/
    mean(case when priceAmzn_crockp = . then priceclust3_crockp end ) as meanprimary1price_amzdelisted,
	mean(case when priceAmzn_crockp = . then priceclust5_crockp end ) as meanprimary2price_amzdelisted,
    mean(case when priceAmzn_crockp > 0 then priceclust3_crockp end) as meanprimary1price_amzlisted,
	mean(case when priceAmzn_crockp > 0 then priceclust5_crockp end) as meanprimary2price_amzlisted,
	mean(case when priceclust3_crockp = . then priceAmzn_crockp end) as meanAmzprice_primary1delisted,
    mean(case when priceclust3_crockp > 0 then priceAmzn_crockp end) as meanAmzprice_primary1listed,
	mean(case when priceclust5_crockp = . then priceAmzn_crockp end) as meanAmzprice_primary2delisted,
    mean(case when priceclust5_crockp > 0 then priceAmzn_crockp end) as meanAmzprice_primary2listed
from buyboxwinner_ID_crockp1;
quit;


/***getting seller rating at seller ID level **/
libname inn 'D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\Cluster Analysis';
/*A19GJQAKBGLP50: EP*/
/*A1RDYXN1JW1DJR: Crock Pot */

data focal_rival_prods23;
set inn.focal_rival_prods23;
where seller_ID in ('A3JAGHYFRKLQJ8', 'A19GJQAKBGLP50', 'A1RDYXN1JW1DJR');
run;

proc means data = focal_rival_prods23 n mean;
class seller_ID;
var seller_rate;
output out = means_rate;
run;
